{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 工具包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#coding=utf-8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:34:54.624167Z",
     "start_time": "2017-09-26T05:34:46.420964Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "\n",
    "#Python SQLITE数据库是一款非常小巧的嵌入式开源数据库软件\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 载入原始数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get more information about the Millionsong project from https://labrosa.ee.columbia.edu/millionsong/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Triplets data  [user, song, play_count]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get the data from http://labrosa.ee.columbia.edu/millionsong/sites/default/files/challenge/train_triplets.txt.zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T09:48:06.127761Z",
     "start_time": "2017-09-24T09:48:05.969929Z"
    }
   },
   "outputs": [],
   "source": [
    "#数据集太大，读10000条记录看看\n",
    "#因为原始数据对应的txt文件，并没有数据，故更新为此csv数据集\n",
    "triplet_dataset = pd.read_csv('./triplet_dataset_sub.csv',encoding='ISO-8859-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T09:48:29.300568Z",
     "start_time": "2017-09-24T09:48:29.275986Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOCKSGZ12A58A7CA4B</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOCVTLJ12A6310F0FD</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SODLLYS12A8C13A96B</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOEGIYH12A6D4FC0E3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOFRQTD12A81C233C0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOHEMBB12A6701E907</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOHJOLH12A6310DFE5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOIZLKI12A6D4F7B61</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOJGSIO12A8C141DBF</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOKEYJQ12A6D4F6132</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  play_count\n",
       "0  4e11f45d732f4861772b2906f81a7d384552ad12  SOCKSGZ12A58A7CA4B           1\n",
       "1  4e11f45d732f4861772b2906f81a7d384552ad12  SOCVTLJ12A6310F0FD           1\n",
       "2  4e11f45d732f4861772b2906f81a7d384552ad12  SODLLYS12A8C13A96B           3\n",
       "3  4e11f45d732f4861772b2906f81a7d384552ad12  SOEGIYH12A6D4FC0E3           1\n",
       "4  4e11f45d732f4861772b2906f81a7d384552ad12  SOFRQTD12A81C233C0           2\n",
       "5  4e11f45d732f4861772b2906f81a7d384552ad12  SOHEMBB12A6701E907           1\n",
       "6  4e11f45d732f4861772b2906f81a7d384552ad12  SOHJOLH12A6310DFE5           1\n",
       "7  4e11f45d732f4861772b2906f81a7d384552ad12  SOIZLKI12A6D4F7B61           1\n",
       "8  4e11f45d732f4861772b2906f81a7d384552ad12  SOJGSIO12A8C141DBF           1\n",
       "9  4e11f45d732f4861772b2906f81a7d384552ad12  SOKEYJQ12A6D4F6132           1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 37519 entries, 0 to 37518\n",
      "Data columns (total 3 columns):\n",
      "user          37519 non-null object\n",
      "song          37519 non-null object\n",
      "play_count    37519 non-null int64\n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 879.4+ KB\n"
     ]
    }
   ],
   "source": [
    "#数据整体一览\n",
    "triplet_dataset.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算每个用户的总播放次数\n",
    "看哪些用户最活跃（play counts最多）\n",
    "由于空间有限，不活跃的用户不考虑了（共有约1M用户）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "790"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_info=triplet_dataset.user.value_counts()\n",
    "#获取到每个用户点击路的索引\n",
    "#用户数量\n",
    "len(user_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获取用户播放音乐的总次数\n",
    "\n",
    "user_info_playcount = pd.DataFrame(columns=['user', 'all_play_count'])\n",
    "\n",
    "for index,userid in zip(range(0,len(user_info.index)-1),user_info.index) :\n",
    "    test_count=triplet_dataset[triplet_dataset['user']==userid].play_count.sum()\n",
    "    user_info_playcount.loc[index]=[userid,test_count]\n",
    "    \n",
    "#按总播放次数排序\n",
    "user_info_playcount = user_info_playcount.sort_values(by = 'all_play_count', ascending = False)\n",
    "user_info_playcount.to_csv(path_or_buf='user_playcount_df.csv', index = False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算每首歌曲的总播放次数\n",
    "看哪些歌曲被播放次数最多（play counts最多）\n",
    "由于空间有限，不受欢迎的歌曲先不考虑（共有约1M用户）\n",
    "\n",
    "其实两个统计次数一起做亦可（如果机器能存储两个字典）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "800"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_info=triplet_dataset.song.value_counts()\n",
    "#获取到每个用户点击路的索引\n",
    "#用户数量\n",
    "len(song_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:00:40.238849Z",
     "start_time": "2017-09-24T09:57:54.875213Z"
    }
   },
   "outputs": [],
   "source": [
    "song_info_playcount = pd.DataFrame(columns=['song', 'all_play_count'])\n",
    "\n",
    "for index,songid in zip(range(0,len(user_info.index)-1),song_info.index) :\n",
    "    test_count=triplet_dataset[triplet_dataset['song']==songid].play_count.sum()\n",
    "    song_info_playcount.loc[index]=[songid,test_count]\n",
    "    \n",
    "    \n",
    "#按总播放次数排序\n",
    "song_info_playcount = song_info_playcount.sort_values(by = 'all_play_count', ascending = False)\n",
    "song_info_playcount.to_csv(path_or_buf='song_info_playcount.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 最活跃的用户和最流行的歌曲"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:08:54.987662Z",
     "start_time": "2017-09-24T10:08:53.518248Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song</th>\n",
       "      <th>all_play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "      <td>9869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SOPUCYA12A8C13A694</td>\n",
       "      <td>3940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SOSXLTC12AF72A7F54</td>\n",
       "      <td>3620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>SOHTKMO12AB01843B0</td>\n",
       "      <td>3171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SOAUWYT12A81C206F1</td>\n",
       "      <td>3153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SOFRQTD12A81C233C0</td>\n",
       "      <td>3112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>SOOFYTN12A6D4F9B35</td>\n",
       "      <td>3051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>SOTWNDJ12A8C143984</td>\n",
       "      <td>2884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SOAXGDH12A8C13F8A1</td>\n",
       "      <td>2697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>SOUNJIO12AB0181339</td>\n",
       "      <td>2621</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  song all_play_count\n",
       "12  SOBONKR12A58A7A7E0           9869\n",
       "5   SOPUCYA12A8C13A694           3940\n",
       "4   SOSXLTC12AF72A7F54           3620\n",
       "16  SOHTKMO12AB01843B0           3171\n",
       "10  SOAUWYT12A81C206F1           3153\n",
       "1   SOFRQTD12A81C233C0           3112\n",
       "27  SOOFYTN12A6D4F9B35           3051\n",
       "46  SOTWNDJ12A8C143984           2884\n",
       "0   SOAXGDH12A8C13F8A1           2697\n",
       "72  SOUNJIO12AB0181339           2621"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_info_playcount.head(n =10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 从总体数据中抽取子集\n",
    "#由于实验用的数据本身已经很小，故所有数据样本都要加入到模型处理中来"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#song_total_play_count = sum(song_info_playcount.all_play_count)\n",
    "#print(song_total_play_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:08:50.652416Z",
     "start_time": "2017-09-24T10:05:53.721519Z"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "#前面百分八十的数据 如下部分，在小数据集无需处理\n",
    "#(float(song_info_playcount.head(n=355).all_play_count.sum())/song_total_play_count)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#song_count_subset = song_info_playcount.head(n=355)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:09:06.800300Z",
     "start_time": "2017-09-24T10:09:06.427869Z"
    }
   },
   "outputs": [],
   "source": [
    "#user_total_play_count = sum(user_info_playcount.all_play_count)\n",
    "#print(user_total_play_count)\n",
    "\n",
    "#前面百分八十的数据\n",
    "#print((float(user_info_playcount.head(n=365).all_play_count.sum())/user_total_play_count)*100)\n",
    "\n",
    "#user_count_subset = user_info_playcount.head(n=365)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:39.337554Z",
     "start_time": "2017-09-24T10:26:39.326529Z"
    }
   },
   "outputs": [],
   "source": [
    "user_subset = list(user_info_playcount.user)\n",
    "song_subset = list(song_info_playcount.song)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:16:05.763243Z",
     "start_time": "2017-09-24T10:16:05.700186Z"
    }
   },
   "outputs": [],
   "source": [
    "triplet_dataset_sub = triplet_dataset[triplet_dataset.user.isin(user_subset) ]\n",
    "del(triplet_dataset)\n",
    "triplet_dataset_sub_song = triplet_dataset_sub[triplet_dataset_sub.song.isin(song_subset)]\n",
    "del(triplet_dataset_sub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:37.537061Z",
     "start_time": "2017-09-24T10:26:37.528055Z"
    }
   },
   "outputs": [],
   "source": [
    "triplet_dataset_sub_song.to_csv(path_or_buf ='triplet_dataset_sub_song.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:38.311410Z",
     "start_time": "2017-09-24T10:26:38.306426Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOCKSGZ12A58A7CA4B</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOCVTLJ12A6310F0FD</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SODLLYS12A8C13A96B</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOEGIYH12A6D4FC0E3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOFRQTD12A81C233C0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOHEMBB12A6701E907</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOHJOLH12A6310DFE5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOIZLKI12A6D4F7B61</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOJGSIO12A8C141DBF</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOKEYJQ12A6D4F6132</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  play_count\n",
       "0  4e11f45d732f4861772b2906f81a7d384552ad12  SOCKSGZ12A58A7CA4B           1\n",
       "1  4e11f45d732f4861772b2906f81a7d384552ad12  SOCVTLJ12A6310F0FD           1\n",
       "2  4e11f45d732f4861772b2906f81a7d384552ad12  SODLLYS12A8C13A96B           3\n",
       "3  4e11f45d732f4861772b2906f81a7d384552ad12  SOEGIYH12A6D4FC0E3           1\n",
       "4  4e11f45d732f4861772b2906f81a7d384552ad12  SOFRQTD12A81C233C0           2\n",
       "5  4e11f45d732f4861772b2906f81a7d384552ad12  SOHEMBB12A6701E907           1\n",
       "6  4e11f45d732f4861772b2906f81a7d384552ad12  SOHJOLH12A6310DFE5           1\n",
       "7  4e11f45d732f4861772b2906f81a7d384552ad12  SOIZLKI12A6D4F7B61           1\n",
       "8  4e11f45d732f4861772b2906f81a7d384552ad12  SOJGSIO12A8C141DBF           1\n",
       "9  4e11f45d732f4861772b2906f81a7d384552ad12  SOKEYJQ12A6D4F6132           1"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:30:23.563673Z",
     "start_time": "2017-09-24T10:27:22.711377Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(37385, 3)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 将歌曲元数据与用户播放记录合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:32:35.248710Z",
     "start_time": "2017-09-24T10:32:35.241709Z"
    }
   },
   "outputs": [],
   "source": [
    "#### 歌曲元数据\n",
    "#Get the data from http://labrosa.ee.columbia.edu/millionsong/sites/default/files/AdditionalFiles/track_metadata.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('songs',)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#输出数据库中表内容\n",
    "conn = sqlite3.connect('track_metadata.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"SELECT name FROM sqlite_master WHERE type='table'\")\n",
    "cur.fetchall()  #数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T13:59:00.464363Z",
     "start_time": "2017-09-24T13:58:47.127394Z"
    }
   },
   "outputs": [],
   "source": [
    "track_metadata_df = pd.read_sql(con=conn, sql='select * from songs')\n",
    "\n",
    "#只保留流行歌曲（3万首）的元数据\n",
    "track_metadata_df_sub = track_metadata_df[track_metadata_df.song_id.isin(song_subset)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:33:25.958644Z",
     "start_time": "2017-09-24T10:33:25.939631Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>track_id</th>\n",
       "      <th>title</th>\n",
       "      <th>song_id</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_mbid</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>artist_familiarity</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "      <th>year</th>\n",
       "      <th>track_7digitalid</th>\n",
       "      <th>shs_perf</th>\n",
       "      <th>shs_work</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2239</th>\n",
       "      <td>TRMWNHM128F9335CA2</td>\n",
       "      <td>Van Helsing Boombox</td>\n",
       "      <td>SOUEGBF12AB017EFD5</td>\n",
       "      <td>Six Demon Bag</td>\n",
       "      <td>ARMI4NV1187B99D55D</td>\n",
       "      <td>4e210d41-b3e3-4e34-8c3b-3b8cc2116562</td>\n",
       "      <td>Man Man</td>\n",
       "      <td>222.45832</td>\n",
       "      <td>0.710877</td>\n",
       "      <td>0.402965</td>\n",
       "      <td>2006</td>\n",
       "      <td>6111372</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3812</th>\n",
       "      <td>TRMGUWH128F146903A</td>\n",
       "      <td>Sincerité Et Jalousie</td>\n",
       "      <td>SOBOUPA12A6D4F81F1</td>\n",
       "      <td>Simple Et Funky</td>\n",
       "      <td>ARZO9UQ1187FB4D261</td>\n",
       "      <td>8bc9464e-5967-477e-a9c3-ad5639d24517</td>\n",
       "      <td>Alliance Ethnik</td>\n",
       "      <td>195.94404</td>\n",
       "      <td>0.509271</td>\n",
       "      <td>0.437972</td>\n",
       "      <td>0</td>\n",
       "      <td>314771</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5998</th>\n",
       "      <td>TRMCMYP128F145BC3D</td>\n",
       "      <td>The Maestro</td>\n",
       "      <td>SOMMONH12A6D4F41CD</td>\n",
       "      <td>Check Your Head</td>\n",
       "      <td>ARLHO5Z1187FB4C861</td>\n",
       "      <td>9beb62b2-88db-4cea-801e-162cd344ee53</td>\n",
       "      <td>Beastie Boys</td>\n",
       "      <td>172.22485</td>\n",
       "      <td>0.853125</td>\n",
       "      <td>0.591628</td>\n",
       "      <td>1992</td>\n",
       "      <td>253738</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6754</th>\n",
       "      <td>TRMCTYQ128E07921DC</td>\n",
       "      <td>Big Yellow Taxi</td>\n",
       "      <td>SOUDGEV12A8C135FC9</td>\n",
       "      <td>Films About Ghosts (The Best Of Counting Crows)</td>\n",
       "      <td>ARLY7P81187B9ACF4D</td>\n",
       "      <td>a0327dc2-dc76-44d5-aec6-47cd2dff1469</td>\n",
       "      <td>Counting Crows / Vanessa Carlton</td>\n",
       "      <td>225.54077</td>\n",
       "      <td>0.778865</td>\n",
       "      <td>0.591740</td>\n",
       "      <td>2002</td>\n",
       "      <td>162713</td>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8048</th>\n",
       "      <td>TRMRIKU128F92FA7C3</td>\n",
       "      <td>You Only Live Once</td>\n",
       "      <td>SOUTGUB12AB017DC5B</td>\n",
       "      <td>You Only Live Once/Mercy Mercy Me</td>\n",
       "      <td>AR9O98A1187FB39621</td>\n",
       "      <td>f181961b-20f7-459e-89de-920ef03c7ed0</td>\n",
       "      <td>The Strokes</td>\n",
       "      <td>187.34975</td>\n",
       "      <td>0.807043</td>\n",
       "      <td>0.587194</td>\n",
       "      <td>2005</td>\n",
       "      <td>4920124</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                track_id                  title             song_id  \\\n",
       "2239  TRMWNHM128F9335CA2    Van Helsing Boombox  SOUEGBF12AB017EFD5   \n",
       "3812  TRMGUWH128F146903A  Sincerité Et Jalousie  SOBOUPA12A6D4F81F1   \n",
       "5998  TRMCMYP128F145BC3D            The Maestro  SOMMONH12A6D4F41CD   \n",
       "6754  TRMCTYQ128E07921DC        Big Yellow Taxi  SOUDGEV12A8C135FC9   \n",
       "8048  TRMRIKU128F92FA7C3     You Only Live Once  SOUTGUB12AB017DC5B   \n",
       "\n",
       "                                              release           artist_id  \\\n",
       "2239                                    Six Demon Bag  ARMI4NV1187B99D55D   \n",
       "3812                                  Simple Et Funky  ARZO9UQ1187FB4D261   \n",
       "5998                                  Check Your Head  ARLHO5Z1187FB4C861   \n",
       "6754  Films About Ghosts (The Best Of Counting Crows)  ARLY7P81187B9ACF4D   \n",
       "8048                You Only Live Once/Mercy Mercy Me  AR9O98A1187FB39621   \n",
       "\n",
       "                               artist_mbid                       artist_name  \\\n",
       "2239  4e210d41-b3e3-4e34-8c3b-3b8cc2116562                           Man Man   \n",
       "3812  8bc9464e-5967-477e-a9c3-ad5639d24517                   Alliance Ethnik   \n",
       "5998  9beb62b2-88db-4cea-801e-162cd344ee53                      Beastie Boys   \n",
       "6754  a0327dc2-dc76-44d5-aec6-47cd2dff1469  Counting Crows / Vanessa Carlton   \n",
       "8048  f181961b-20f7-459e-89de-920ef03c7ed0                       The Strokes   \n",
       "\n",
       "       duration  artist_familiarity  artist_hotttnesss  year  \\\n",
       "2239  222.45832            0.710877           0.402965  2006   \n",
       "3812  195.94404            0.509271           0.437972     0   \n",
       "5998  172.22485            0.853125           0.591628  1992   \n",
       "6754  225.54077            0.778865           0.591740  2002   \n",
       "8048  187.34975            0.807043           0.587194  2005   \n",
       "\n",
       "      track_7digitalid  shs_perf  shs_work  \n",
       "2239           6111372        -1         0  \n",
       "3812            314771        -1         0  \n",
       "5998            253738        -1         0  \n",
       "6754            162713        21        20  \n",
       "8048           4920124        -1         0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T13:48:12.545636Z",
     "start_time": "2017-09-24T13:48:12.536631Z"
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>track_id</th>\n",
       "      <th>title</th>\n",
       "      <th>song_id</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_mbid</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>artist_familiarity</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "      <th>year</th>\n",
       "      <th>track_7digitalid</th>\n",
       "      <th>shs_perf</th>\n",
       "      <th>shs_work</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2239</th>\n",
       "      <td>TRMWNHM128F9335CA2</td>\n",
       "      <td>Van Helsing Boombox</td>\n",
       "      <td>SOUEGBF12AB017EFD5</td>\n",
       "      <td>Six Demon Bag</td>\n",
       "      <td>ARMI4NV1187B99D55D</td>\n",
       "      <td>4e210d41-b3e3-4e34-8c3b-3b8cc2116562</td>\n",
       "      <td>Man Man</td>\n",
       "      <td>222.45832</td>\n",
       "      <td>0.710877</td>\n",
       "      <td>0.402965</td>\n",
       "      <td>2006</td>\n",
       "      <td>6111372</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3812</th>\n",
       "      <td>TRMGUWH128F146903A</td>\n",
       "      <td>Sincerité Et Jalousie</td>\n",
       "      <td>SOBOUPA12A6D4F81F1</td>\n",
       "      <td>Simple Et Funky</td>\n",
       "      <td>ARZO9UQ1187FB4D261</td>\n",
       "      <td>8bc9464e-5967-477e-a9c3-ad5639d24517</td>\n",
       "      <td>Alliance Ethnik</td>\n",
       "      <td>195.94404</td>\n",
       "      <td>0.509271</td>\n",
       "      <td>0.437972</td>\n",
       "      <td>0</td>\n",
       "      <td>314771</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5998</th>\n",
       "      <td>TRMCMYP128F145BC3D</td>\n",
       "      <td>The Maestro</td>\n",
       "      <td>SOMMONH12A6D4F41CD</td>\n",
       "      <td>Check Your Head</td>\n",
       "      <td>ARLHO5Z1187FB4C861</td>\n",
       "      <td>9beb62b2-88db-4cea-801e-162cd344ee53</td>\n",
       "      <td>Beastie Boys</td>\n",
       "      <td>172.22485</td>\n",
       "      <td>0.853125</td>\n",
       "      <td>0.591628</td>\n",
       "      <td>1992</td>\n",
       "      <td>253738</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6754</th>\n",
       "      <td>TRMCTYQ128E07921DC</td>\n",
       "      <td>Big Yellow Taxi</td>\n",
       "      <td>SOUDGEV12A8C135FC9</td>\n",
       "      <td>Films About Ghosts (The Best Of Counting Crows)</td>\n",
       "      <td>ARLY7P81187B9ACF4D</td>\n",
       "      <td>a0327dc2-dc76-44d5-aec6-47cd2dff1469</td>\n",
       "      <td>Counting Crows / Vanessa Carlton</td>\n",
       "      <td>225.54077</td>\n",
       "      <td>0.778865</td>\n",
       "      <td>0.591740</td>\n",
       "      <td>2002</td>\n",
       "      <td>162713</td>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8048</th>\n",
       "      <td>TRMRIKU128F92FA7C3</td>\n",
       "      <td>You Only Live Once</td>\n",
       "      <td>SOUTGUB12AB017DC5B</td>\n",
       "      <td>You Only Live Once/Mercy Mercy Me</td>\n",
       "      <td>AR9O98A1187FB39621</td>\n",
       "      <td>f181961b-20f7-459e-89de-920ef03c7ed0</td>\n",
       "      <td>The Strokes</td>\n",
       "      <td>187.34975</td>\n",
       "      <td>0.807043</td>\n",
       "      <td>0.587194</td>\n",
       "      <td>2005</td>\n",
       "      <td>4920124</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                track_id                  title             song_id  \\\n",
       "2239  TRMWNHM128F9335CA2    Van Helsing Boombox  SOUEGBF12AB017EFD5   \n",
       "3812  TRMGUWH128F146903A  Sincerité Et Jalousie  SOBOUPA12A6D4F81F1   \n",
       "5998  TRMCMYP128F145BC3D            The Maestro  SOMMONH12A6D4F41CD   \n",
       "6754  TRMCTYQ128E07921DC        Big Yellow Taxi  SOUDGEV12A8C135FC9   \n",
       "8048  TRMRIKU128F92FA7C3     You Only Live Once  SOUTGUB12AB017DC5B   \n",
       "\n",
       "                                              release           artist_id  \\\n",
       "2239                                    Six Demon Bag  ARMI4NV1187B99D55D   \n",
       "3812                                  Simple Et Funky  ARZO9UQ1187FB4D261   \n",
       "5998                                  Check Your Head  ARLHO5Z1187FB4C861   \n",
       "6754  Films About Ghosts (The Best Of Counting Crows)  ARLY7P81187B9ACF4D   \n",
       "8048                You Only Live Once/Mercy Mercy Me  AR9O98A1187FB39621   \n",
       "\n",
       "                               artist_mbid                       artist_name  \\\n",
       "2239  4e210d41-b3e3-4e34-8c3b-3b8cc2116562                           Man Man   \n",
       "3812  8bc9464e-5967-477e-a9c3-ad5639d24517                   Alliance Ethnik   \n",
       "5998  9beb62b2-88db-4cea-801e-162cd344ee53                      Beastie Boys   \n",
       "6754  a0327dc2-dc76-44d5-aec6-47cd2dff1469  Counting Crows / Vanessa Carlton   \n",
       "8048  f181961b-20f7-459e-89de-920ef03c7ed0                       The Strokes   \n",
       "\n",
       "       duration  artist_familiarity  artist_hotttnesss  year  \\\n",
       "2239  222.45832            0.710877           0.402965  2006   \n",
       "3812  195.94404            0.509271           0.437972     0   \n",
       "5998  172.22485            0.853125           0.591628  1992   \n",
       "6754  225.54077            0.778865           0.591740  2002   \n",
       "8048  187.34975            0.807043           0.587194  2005   \n",
       "\n",
       "      track_7digitalid  shs_perf  shs_work  \n",
       "2239           6111372        -1         0  \n",
       "3812            314771        -1         0  \n",
       "5998            253738        -1         0  \n",
       "6754            162713        21        20  \n",
       "8048           4920124        -1         0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'sys' (built-in)>"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# -*- coding:utf-8 -*-\n",
    "import sys\n",
    "import importlib\n",
    "importlib.reload(sys)\n",
    "\n",
    "\n",
    "#sys.setdefaultencoding(\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T14:00:17.841834Z",
     "start_time": "2017-09-24T14:00:17.450258Z"
    }
   },
   "outputs": [],
   "source": [
    "track_metadata_df_sub.to_csv(path_or_buf ='track_metadata_df_sub.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T14:00:26.155770Z",
     "start_time": "2017-09-24T14:00:26.149763Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(838, 14)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load up the saved data subsets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:13.855787Z",
     "start_time": "2017-09-26T05:35:00.267829Z"
    }
   },
   "outputs": [],
   "source": [
    "triplet_dataset_sub_song = pd.read_csv(filepath_or_buffer = 'triplet_dataset_sub_song.csv')\n",
    "track_metadata_df_sub = pd.read_csv(filepath_or_buffer = 'track_metadata_df_sub.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:26.643464Z",
     "start_time": "2017-09-26T05:35:14.956760Z"
    }
   },
   "outputs": [],
   "source": [
    "del(track_metadata_df_sub['track_id'])\n",
    "del(track_metadata_df_sub['artist_mbid'])\n",
    "\n",
    "#去除重复记录\n",
    "track_metadata_df_sub = track_metadata_df_sub.drop_duplicates(['song_id'])\n",
    "\n",
    "#播放历史和歌曲元数据合并\n",
    "triplet_dataset_sub_song_merged = pd.merge(triplet_dataset_sub_song, track_metadata_df_sub, how='left', left_on='song', right_on='song_id')\n",
    "triplet_dataset_sub_song_merged.rename(columns={'play_count':'listen_count'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:31.415206Z",
     "start_time": "2017-09-26T05:35:29.271749Z"
    }
   },
   "outputs": [],
   "source": [
    "#去掉一些不用的列\n",
    "del(triplet_dataset_sub_song_merged['song_id'])\n",
    "del(triplet_dataset_sub_song_merged['artist_id'])\n",
    "del(triplet_dataset_sub_song_merged['duration'])\n",
    "del(triplet_dataset_sub_song_merged['artist_familiarity'])\n",
    "del(triplet_dataset_sub_song_merged['artist_hotttnesss'])\n",
    "del(triplet_dataset_sub_song_merged['track_7digitalid'])\n",
    "del(triplet_dataset_sub_song_merged['shs_perf'])\n",
    "del(triplet_dataset_sub_song_merged['shs_work'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T14:01:44.587532Z",
     "start_time": "2017-09-24T14:01:44.561014Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOCKSGZ12A58A7CA4B</td>\n",
       "      <td>1</td>\n",
       "      <td>Bleed It Out [Live At Milton Keynes]</td>\n",
       "      <td>Road To Revolution: Live At Milton Keynes</td>\n",
       "      <td>Linkin Park</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOCVTLJ12A6310F0FD</td>\n",
       "      <td>1</td>\n",
       "      <td>Clocks</td>\n",
       "      <td>A Rush Of Blood To The Head</td>\n",
       "      <td>Coldplay</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SODLLYS12A8C13A96B</td>\n",
       "      <td>3</td>\n",
       "      <td>Breakeven</td>\n",
       "      <td>Now That's What I Call Music! 72</td>\n",
       "      <td>The Script</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOEGIYH12A6D4FC0E3</td>\n",
       "      <td>1</td>\n",
       "      <td>Horn Concerto No. 4 in E flat K495: II. Romanc...</td>\n",
       "      <td>Mozart - Eine kleine Nachtmusik</td>\n",
       "      <td>Barry Tuckwell/Academy of St Martin-in-the-Fie...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOFRQTD12A81C233C0</td>\n",
       "      <td>2</td>\n",
       "      <td>Sehr kosmisch</td>\n",
       "      <td>Musik von Harmonia</td>\n",
       "      <td>Harmonia</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOHEMBB12A6701E907</td>\n",
       "      <td>1</td>\n",
       "      <td>Superman</td>\n",
       "      <td>The Eminem Show</td>\n",
       "      <td>Eminem / Dina Rae</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOHJOLH12A6310DFE5</td>\n",
       "      <td>1</td>\n",
       "      <td>Karma Police</td>\n",
       "      <td>OK Computer (Collector's Edition)</td>\n",
       "      <td>Radiohead</td>\n",
       "      <td>1997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOIZLKI12A6D4F7B61</td>\n",
       "      <td>1</td>\n",
       "      <td>Supermassive Black Hole (Twilight Soundtrack V...</td>\n",
       "      <td>Music From The Original Motion Picture Soundtrack</td>\n",
       "      <td>Muse</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOJGSIO12A8C141DBF</td>\n",
       "      <td>1</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Only By The Night</td>\n",
       "      <td>Kings Of Leon</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4e11f45d732f4861772b2906f81a7d384552ad12</td>\n",
       "      <td>SOKEYJQ12A6D4F6132</td>\n",
       "      <td>1</td>\n",
       "      <td>Smile Like You Mean It</td>\n",
       "      <td>Hot Fuss</td>\n",
       "      <td>The Killers</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  listen_count  \\\n",
       "0  4e11f45d732f4861772b2906f81a7d384552ad12  SOCKSGZ12A58A7CA4B             1   \n",
       "1  4e11f45d732f4861772b2906f81a7d384552ad12  SOCVTLJ12A6310F0FD             1   \n",
       "2  4e11f45d732f4861772b2906f81a7d384552ad12  SODLLYS12A8C13A96B             3   \n",
       "3  4e11f45d732f4861772b2906f81a7d384552ad12  SOEGIYH12A6D4FC0E3             1   \n",
       "4  4e11f45d732f4861772b2906f81a7d384552ad12  SOFRQTD12A81C233C0             2   \n",
       "5  4e11f45d732f4861772b2906f81a7d384552ad12  SOHEMBB12A6701E907             1   \n",
       "6  4e11f45d732f4861772b2906f81a7d384552ad12  SOHJOLH12A6310DFE5             1   \n",
       "7  4e11f45d732f4861772b2906f81a7d384552ad12  SOIZLKI12A6D4F7B61             1   \n",
       "8  4e11f45d732f4861772b2906f81a7d384552ad12  SOJGSIO12A8C141DBF             1   \n",
       "9  4e11f45d732f4861772b2906f81a7d384552ad12  SOKEYJQ12A6D4F6132             1   \n",
       "\n",
       "                                               title  \\\n",
       "0               Bleed It Out [Live At Milton Keynes]   \n",
       "1                                             Clocks   \n",
       "2                                          Breakeven   \n",
       "3  Horn Concerto No. 4 in E flat K495: II. Romanc...   \n",
       "4                                      Sehr kosmisch   \n",
       "5                                           Superman   \n",
       "6                                       Karma Police   \n",
       "7  Supermassive Black Hole (Twilight Soundtrack V...   \n",
       "8                                          Manhattan   \n",
       "9                             Smile Like You Mean It   \n",
       "\n",
       "                                             release  \\\n",
       "0          Road To Revolution: Live At Milton Keynes   \n",
       "1                        A Rush Of Blood To The Head   \n",
       "2                   Now That's What I Call Music! 72   \n",
       "3                    Mozart - Eine kleine Nachtmusik   \n",
       "4                                 Musik von Harmonia   \n",
       "5                                    The Eminem Show   \n",
       "6                  OK Computer (Collector's Edition)   \n",
       "7  Music From The Original Motion Picture Soundtrack   \n",
       "8                                  Only By The Night   \n",
       "9                                           Hot Fuss   \n",
       "\n",
       "                                         artist_name  year  \n",
       "0                                        Linkin Park  2008  \n",
       "1                                           Coldplay  2002  \n",
       "2                                         The Script  2008  \n",
       "3  Barry Tuckwell/Academy of St Martin-in-the-Fie...     0  \n",
       "4                                           Harmonia     0  \n",
       "5                                  Eminem / Dina Rae  2003  \n",
       "6                                          Radiohead  1997  \n",
       "7                                               Muse     0  \n",
       "8                                      Kings Of Leon  2008  \n",
       "9                                        The Killers  2003  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song_merged.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 推荐系统使用的数据集\n",
    "triplet_dataset_sub_song_merged.to_csv(path_or_buf ='triplet_dataset_sub_song_merged.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  },
  "toc": {
   "nav_menu": {
    "height": "153px",
    "width": "160px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": true,
   "toc_position": {
    "height": "691px",
    "left": "0px",
    "right": "1405px",
    "top": "106px",
    "width": "303px"
   },
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
